<?php
class Application_Model_Mapper_Account extends Application_Model_Mapper_Abstract
{

	/**
	* Get Db table object
	*
	* @return Model_DbTable_Inventory
	*/
	protected $_dbName;
	public function __construct()
	{
		parent::__construct();
		$this->_dbName = $this->_db_ovis_name.'.accounts';
	}
	
	/**
	* Get Db table object
	*
	* @return Model_DbTable_Account
	*/
	public function getDbTable()
	{
		if (null === $this->_dbTable) {
			$this->setDbTable('Application_Model_DbTable_Account');
		}
		return $this->_dbTable;
	}

	/**
	* Map row object data to the site object properties
	*
	* @param  Zend_Db_Table_Row $row
	* @throws Exception
	* @return Model_Account mapped object
	*/
	public function mapData($row)
	{
		if (!$row instanceof Zend_Db_Table_Row) {
			throw new Exception('Invalid table row provided');
		}
		$user = new Application_Model_Account();
		$user->setAccountId($row['accountid'])
		->setUsername($row['username'])
		->setPassword($row['password'])
		->setFullname($row['fullname'])
		->setEmail($row['email'])
		->setAccountType($row['accounttype'])
		->setIsActivated($row['isactivated'])
		->setIsBlocked($row['isblocked'])
		->setLastestLogin($row['lastest_login'])
		->setIsSystem($row['issystem'])
		->setIsarchived($row['isarchived'])
		->setSalt($row['salt'])
		->setActivateCode($row['activatecode'])
		->setOtrsUserId($row['otrs_user_id']);
		return $user;
	}

	/**
	 * Save Vendor object in database
	 *
	 * @param Model_Account $account
	 *
	 * @return int The primary key of the row inserted or the number of rows updated
	 */
	public function save($account)
	{
		parent::save($account);
		$data = array(
				'accountid'       => $account->getAccountId(),
				'username'     => $account->getUsername(),
				'password'        => $account->getPassword(),
				'fullname'        => $account->getFullname(),
				'email'          => $account->getEmail(),
				'accounttype'   => $account->getAccountType(),
				'isactivated'     => $account->getIsActivated(),
				'isblocked'          => $account->getIsBlocked(),
				'lastest_login'        => $account->getLastestLogin(),
				'issystem'         => $account->getIsSystem(),
				'isarchived'     => $account->getIsarchived(),
				'salt'     => $account->getSalt(),
				'activatecode'   => $account->getActivateCode(),
				'otrs_user_id'   => $account->getOtrsUserId()
		);
		try {
			if (NULL === ($id = $account->getAccountId())) {
				unset($data['accountid']);
				$account->setAccountId($this->getDbTable()->insert($data));
				return $account->getAccountId();
			} else {
				return $this->getDbTable()->update($data, array('accountid = ?' => $id));
			}
		} catch (Exception $e) {
			Sss_Ultility_Common::logger()->log($e, Zend_Log::ERR);
		}
	}

	/**
	* Fetch all Account
	*
	* @return array List of Model_Account
	*/
	public function fetchAll()
	{
		$resultSet = $this->getDbTable()->fetchAll();
		$entries   = array();
		foreach ($resultSet as $row) {
			$entries[] = $this->mapData($row);
		}
		return $entries;
	}

	/**
	* Fetch a Site Account by Identifier
	*
	* @param $id
	*
	* @return Model_Account
	*/
	public function findById($id)
	{
		$select	= $this->getDbTable()->select()->where('accountid = ?', $id);
		$row = $this->getDbTable()->fetchRow($select);
		$entry   = NULL;
		if (!empty($row)) {
			$entry = $this->mapData($row);
		}
		return $entry;
	}

	public function deleteRow($accountId)
	{
		return $this->getDbTable()->delete(array('accountid = ?' => $accountId));
	}
	
	/**
	* put your comment there...
	* 
	* @param mixed $userName
	* @return Model_Account
	*/
	public function getByUserName($userName)
	{
		$select = $this->getDbTable()->select()->where('username = ?', $userName);
		$row = $this->getDbTable()->fetchRow($select);
		$entry   = NULL;
		if (!empty($row)) {
			$entry = $this->mapData($row);
		}
		return $entry;
	}

	public function getCompanyOwnerByStockLocation($stockLocation)
	{
		$where = array();
		$where[] = " sc.stocklocation = ".$stockLocation;
		$where[] = " a.isactivated = 1";
		$where[] = " a.isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$row = $this->loadAll('
					SELECT 
						* 
					FROM 
						' . $this->_dbName.' a
					INNER JOIN
						stocklocationcompanyowners sc on sc.companyowner = a.accountid
					INNER JOIN
						stocklocations s on s.stocklocationid = sc.stocklocation'.$where, false);
		return $row;
	}

	/**
	 * Get account select
	 * @param array $params
	 * @return Zend_Db_Table_Select
	 */
	public function getListSearchByType($limit = '', $limitstart = 0, $filterParam = array())
	{
		$where = array();
	
		if (isset($filterParam['username'])) {
			$where[] = 'username LIKE "%' . $filterParam['username'].'%"';
		}
		if (isset($filterParam['fullname'])) {
			$where[] = 'fullname LIKE "%' . $filterParam['fullname'].'%"';
		}
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';

		$getPagination = Sss_Ultility_Common::getPagination($limit, $limitstart, $filterParam['orderfield'],$filterParam['ordertype'],$filterParam['page'], 'accountid');
		$orderBy    = $getPagination['orderby'];
		$limit      = $getPagination['limit'];
		$limitstart = $getPagination['limitstart'];
		$pagString  = $getPagination['pagstring'];

		$query = "
			SELECT 
				* 
			FROM
				(SELECT DISTINCT 
					a.* ,
					CASE WHEN 
						sc.stocklocation = ".$filterParam['stocklocation']." 
					THEN 
						1 
					ELSE 
						0 
					END 
						as isstock
				FROM 
					accounts a
				INNER JOIN 
					`stocklocationcompanyowners` sc ON a.accountid = sc.companyowner
				WHERE 
					sc.stocklocation = ".$filterParam['stocklocation']." and a.accounttype = ".$filterParam['accounttype']." and isarchived = 0 and isactivated = 1
	
				UNION
	
				SELECT DISTINCT 
					a.*, 0 as isstock
				FROM 
					accounts a
				WHERE 
					a.accountid not in
						(
							SELECT distinct 
								sc.companyowner
							FROM 
								accounts ac
							INNER JOIN 
								`stocklocationcompanyowners` sc on ac.accountid = sc.companyowner
							WHERE 
								sc.stocklocation = ".$filterParam['stocklocation']." and ac.accounttype = ".$filterParam['accounttype']
						.") 
				AND 
					a.accounttype = ".$filterParam['accounttype']." and isarchived = 0 and isactivated = 1
				)
			as Temp ";

		$rows = $this->loadAll($query . $orderBy . $pagString, false);
		$rowAll = $this->loadAll($query . $where . $orderBy, false);
		$result             = new stdclass();
		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));
		//return list
		$result->list       = $rows;
		return $result;
	}
	
	/**
	 * Get account select
	 * @param array $params
	 * @return Zend_Db_Table_Select
	 */
	public function getListByType($limit = '', $limitstart = 0, $filterParam = array())
	{
		$where = array();
		if (isset($filterParam['accounttype']) && (int)$filterParam['accounttype']) {
			$where[] = 'accounttype = ' . $filterParam['accounttype'];
		}
		if (isset($filterParam['username'])) {
			$where[] = 'username like "%' . $filterParam['username'].'%"';
		}
		if (isset($filterParam['fullname'])) {
			$where[] = 'fullname like "%' . $filterParam['fullname'].'%"';
		}
		if (isset($filterParam['isactivated'])) {
			$where[] = 'isactivated ='.$filterParam['isactivated'];
		}
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
	
		$getPagination = Sss_Ultility_Common::getPagination($limit, $limitstart, $filterParam['orderfield'],$filterParam['ordertype'],$filterParam['page'], 'accountid');
		$orderBy    = $getPagination['orderby'];
		$limit      = $getPagination['limit'];
		$limitstart = $getPagination['limitstart'];
		$pagString  = $getPagination['pagstring'];
	
		$query = '
					SELECT 
						*, s.typename, 0 as isstock 
					FROM 
						' . $this->_dbName . ' a 
					INNER JOIN 
						' . $this->_db_ovis_name . '.systemtypes s ON a.accounttype=s.systemtypeid ';
		$rows = $this->loadAll($query . $where . $orderBy . $pagString, false);
		$rowAll = $this->loadAll($query . $where . $orderBy, false);
		$result             = new stdclass();

		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));
	
		//return list
		$result->list       = $rows;
		return $result;
	}

	/**
	 * Get account select
	 * @param array $params
	 * @return Zend_Db_Table_Select
	 */
	public function getAccountListByType($accounttype = null)
	{
		$where = array();
		if ($accounttype != null) {
			$where[] = 'accounttype = ' . $accounttype;
		}
		$where[] = " isactivated = 1";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$orderBy = ' order by accountid desc ';
		$rowAll = $this->loadAll('
					SELECT 
						* 
					FROM 
						' . $this->_dbName . 
					$where . $orderBy, false);
		//return list
		return $rowAll;
	}
	
	public function getById($accountId= null,$accountType = null)
	{
		$where= array();
		if($accountId != null)
			$where[] = " a.accountid =".$accountId;
		$where[] = " a.isarchived = 0 ";
		if($accountType != null && $accountType != Sss_Ultility_Setting::ACCOUNT_VENDOR) {
			$where[] = " v.vendorid is not null";
		}
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
					SELECT 
						DISTINCT a.*,v.vendorid, v.vendorname 
					FROM 
						".$this->_dbName.' a
					LEFT JOIN 
						vendoraccounts va on va.account = a.accountid
					LEFT JOIN 
						vendors v on va.vendor = v.vendorid'
					.$where);
		return $user;
	}

	public function getUserList($userId = null)
	{
		$where = array();
		if($userId != null)
			$where[] = ' accountid not in('.$userId.')';
		$where[] = " isactivated = 1";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$rowAll = $this->loadAll('
					SELECT 
						* 
					FROM ' . $this->_dbName
					.$where, false);
		return $rowAll;
	}

	public function getAccountTypeByAccountId($accountId = null)
	{
		$where = '';
		if(!empty($accountId)) {
			$where = " WHERE accountid = ".$accountId;
		}
		$user = $this->fetchObject("
					SELECT
						accounttype
					FROM
					".$this->_dbName.' a '
					.$where);
		return $user;
	}

	public function getUser($userName = null)
	{
		$where= array();
		if($userName != null)
			$where[] = " isactivated = 1 ";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$query = $this->_db->quoteInto("select * from ".$this->_dbName.$where." and username = ?",$userName);
		$user = $this->fetchObject($query);
		return $user;
	}

	public function getUserByEmail($email = null)
	{
		$where= array();
		if($email != null)
			$where[] = " email = '".$email."'";
		$where[] = " isactivated = 1";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
					SELECT 
						* 
					FROM 
						".$this->_dbName
					.$where
				);
		return $user;
	}
	
	public function getAccountType()
	{
		$type = $this->loadAll("
					SELECT 
						* 
					FROM 
						".$this->_dbName." 
					WHERE 
						isarchived = 0 
					GROUP BY 
						accounttype
					"
		);
		return $type;
	}
	
	public function getUserByActiveCode($activeCode)
	{
		$where = array();
		$where[] = " activatecode= '".$activeCode."'";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
						SELECT 
							* 
						FROM 
						".$this->_dbName
						.$where
				);
		return $user;
	}
	
	public function checkEsixtedColumns($columnName = '')
	{
		$query = "SHOW COLUMNS FROM `accounts` LIKE '".$columnName."'";
		$res = $this->fetchObject($query, false);
		if($res == false)
		{
			$addCol = "ALTER TABLE `accounts` add column ".$columnName." int";
			$this->executeQuery($addCol, false);
		}
	}
}

